package com.lb.jwitter.action;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;

import com.lb.jwitter.service.CommonService;
import com.lb.jwitter.service.ConditionReport;
import com.lb.jwitter.service.Qresult;
import com.lb.jwitter.service.TblDKMHKetqua;
import com.lb.jwitter.service.TblDKMHKetquaService;
import com.lb.jwitter.service.TblQuyenChiTietService;
import com.lb.jwitter.service.TblTaiKhoan;
import com.lb.jwitter.util.ApplicationContextProvider;
import com.lb.jwitter.util.Message;
import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

/**
 * @author QUOCVIET
 * 
 */

@ContextConfiguration(locations = "classpath:beans.xml")
public class TblDKMHKetquaExportExcelAction {

	private String dataString;

	public void setDataString(String dataString) {
		this.dataString = dataString;
	}

	public String getDataString() {
		return dataString;
	}

	private Message msg;

	public Message getMsg() {
		return msg;
	}

	private int dotDKId;

	public void setDotDKId(int dotDKId) {
		this.dotDKId = dotDKId;
	}

	public int getDotDKId() {
		return dotDKId;
	}

	private int hocVienId;

	public void setHocVienId(int hocVienId) {
		this.hocVienId = hocVienId;
	}

	public int getHocVienId() {
		return hocVienId;
	}

	private int nLMHId;

	public void setNLMHId(int nLMHId) {
		this.nLMHId = nLMHId;
	}

	public int getNLMHId() {
		return nLMHId;
	}

	private String ketQua;

	public void setKetQua(String ketQua) {
		this.ketQua = ketQua;
	}

	public String getKetQua() {
		return ketQua;
	}

	private String tungayDangKy;

	public void setTuNgayDangKy(String tungayDangKy) {
		this.tungayDangKy = tungayDangKy;
	}

	public String getTuNgayDangKy() {
		return tungayDangKy;
	}

	private String denngayDangKy;

	public void setDenNgayDangKy(String denngayDangKy) {
		this.denngayDangKy = denngayDangKy;
	}

	public String getDenNgayDangKy() {
		return denngayDangKy;
	}

	private String tungayCapNhat;

	public void setTuNgayCapNhat(String tungayCapNhat) {
		this.tungayCapNhat = tungayCapNhat;
	}

	public String getTuNgayCapNhat() {
		return tungayCapNhat;
	}

	private String denngayCapNhat;

	public void setDenNgayCapNhat(String denngayCapNhat) {
		this.denngayCapNhat = denngayCapNhat;
	}

	public String getDenNgayCapNhat() {
		return denngayCapNhat;
	}

	private int xacNhanYN;

	public void setXacNhanYN(int xacNhanYN) {
		this.xacNhanYN = xacNhanYN;
	}

	public int getXacNhanYN() {
		return xacNhanYN;
	}

	private String tinhTrang;

	public void setTinhTrang(String tinhTrang) {
		this.tinhTrang = tinhTrang;
	}

	public String getTinhTrang() {
		return tinhTrang;
	}

	private String hoTen;

	public String getHoTen() {
		return hoTen;
	}

	public void setHoTen(String hoTen) {
		this.hoTen = hoTen;
	}

	private String mssv;

	public String getMssv() {
		return mssv;
	}

	public void setMssv(String mssv) {
		this.mssv = mssv;
	}

	private List<TblDKMHKetqua> tblDKMHKetquas;

	public List<TblDKMHKetqua> getTblDKMHKetquas() {
		return tblDKMHKetquas;
	}

	private TblDKMHKetquaService tblDKMHKetquaService;

	private int preSearch(Message msg, List<ConditionReport> cons) {
		msg.setPreMessage_b(true);
		msg.setPreMessage_s("success");
		return 1;
	}

	private int postSearch(Message msg, List<ConditionReport> cons) {
		msg.setPostMessage_b(true);
		msg.setPostMessage_s("success");
		return 1;
	}

	private String maNhomLop;

	public String getMaNhomLop() {
		return maNhomLop;
	}

	public void setMaNhomLop(String maNhomLop) {
		this.maNhomLop = maNhomLop;
	}

	private String maMonHoc;

	public String getTungayDangKy() {
		return tungayDangKy;
	}

	public void setTungayDangKy(String tungayDangKy) {
		this.tungayDangKy = tungayDangKy;
	}

	public String getDenngayDangKy() {
		return denngayDangKy;
	}

	public void setDenngayDangKy(String denngayDangKy) {
		this.denngayDangKy = denngayDangKy;
	}

	public String getTungayCapNhat() {
		return tungayCapNhat;
	}

	public void setTungayCapNhat(String tungayCapNhat) {
		this.tungayCapNhat = tungayCapNhat;
	}

	public String getDenngayCapNhat() {
		return denngayCapNhat;
	}

	public void setDenngayCapNhat(String denngayCapNhat) {
		this.denngayCapNhat = denngayCapNhat;
	}

	public String getMaMonHoc() {
		return maMonHoc;
	}

	public void setMaMonHoc(String maMonHoc) {
		this.maMonHoc = maMonHoc;
	}

	private int xacNhanYNAll;

	public int getXacNhanYNAll() {
		return xacNhanYNAll;
	}

	public void setXacNhanYNAll(int xacNhanYNAll) {
		this.xacNhanYNAll = xacNhanYNAll;
	}

	private String filename;

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	private static final int defaultRowHeight = 400;
	private static final int defaultColWidth = 14;

	private int columnWidth;
	private int rowHeight;

	private TblTaiKhoan taiKhoanDangNhap;

	public TblTaiKhoan getTaiKhoanDangNhap() {
		return taiKhoanDangNhap;
	}

	public void setTaiKhoanDangNhap(TblTaiKhoan taiKhoanDangNhap) {
		this.taiKhoanDangNhap = taiKhoanDangNhap;
	}

	private TblQuyenChiTietService tblQuyenChiTietService;
	@Autowired
	// Injected by Spring
	ApplicationContextProvider contextProvider;

	public String execute() throws Exception {
		try {
			Map session = ActionContext.getContext().getSession();
			taiKhoanDangNhap = (TblTaiKhoan) session.get("taiKhoan");
			if (taiKhoanDangNhap == null)
				return "noprivilege";
			ApplicationContext context = ApplicationContextProvider
					.getApplicationContext();

			tblQuyenChiTietService = (TblQuyenChiTietService) context
					.getBean("tbl_Quyen_ChiTietClient");

			if (!tblQuyenChiTietService.checkRight(taiKhoanDangNhap
					.getMaNhomQuyen().getId(), "tbl_DKMH_Ketqua", "xem")) {
				return "noprivilege";
			}
			CommonService commonService = (CommonService) context.getBean("commonClient");
			
			tblDKMHKetquaService = (TblDKMHKetquaService) context
					.getBean("tbl_DKMH_KetquaClient");
			msg = new Message();

			List<ConditionReport> cons = new ArrayList<ConditionReport>();
			if (dotDKId != 0) {
				ConditionReport con = new ConditionReport();
				con.setCol("dotDKId.id");
				con.setValue1(new Long(dotDKId));
				con.setOperator("eq");
				cons.add(con);
			} else {
				return Action.ERROR;
			}
			if (xacNhanYN != -1) {
				if (xacNhanYN == 1) {
					ConditionReport con = new ConditionReport();
					con.setCol("xacNhanYN");
					con.setValue1(true);
					con.setOperator("eq");
					cons.add(con);
				} else {
					ConditionReport con = new ConditionReport();
					con.setCol("xacNhanYN");
					con.setValue1(false);
					con.setOperator("eq");
					cons.add(con);
				}
			}
			if (mssv != null && !mssv.equals("")) {
				ConditionReport con = new ConditionReport();
				con.setCol("hocVienId.MSSV");
				con.setValue1("" + mssv + "%");
				con.setOperator("like");
				cons.add(con);
			}
			if (maNhomLop != null && !maNhomLop.equals("")) {
				ConditionReport con = new ConditionReport();
				con.setCol("NLMHId.ma");
				con.setValue1("%" + maNhomLop + "%");
				con.setOperator("like");
				cons.add(con);
			}
			if (maMonHoc != null && !maMonHoc.equals("")) {
				ConditionReport con = new ConditionReport();
				con.setCol("NLMHId.monHocId.ma");
				con.setValue1("%" + maMonHoc + "%");
				con.setOperator("like");
				cons.add(con);
			}

			if (ketQua != null && !ketQua.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setCol("ketQua");
				con.setValue1("%" + ketQua + "%");
				con.setOperator("like");
				cons.add(con);
			}
			if (tungayDangKy != null && !tungayDangKy.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setType("date");
				con.setCol("ngayDangKy");
				con.setValue1(tungayDangKy);
				if (denngayDangKy != null
						&& !denngayDangKy.equalsIgnoreCase("")) {
					con.setValue2(denngayDangKy);
					con.setOperator("between");
				} else {
					con.setOperator("ge");
				}
				cons.add(con);
			} else if (denngayDangKy != null
					&& !denngayDangKy.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setType("date");
				con.setCol("ngayDangKy");
				con.setValue1(denngayDangKy);
				con.setOperator("le");
				cons.add(con);
			}
			if (tungayCapNhat != null && !tungayCapNhat.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setType("date");
				con.setCol("ngayCapNhat");
				con.setValue1(tungayCapNhat);
				if (denngayCapNhat != null
						&& !denngayCapNhat.equalsIgnoreCase("")) {
					con.setValue2(denngayCapNhat);
					con.setOperator("between");
				} else {
					con.setOperator("ge");
				}
				cons.add(con);
			} else if (denngayCapNhat != null
					&& !denngayCapNhat.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setType("date");
				con.setCol("ngayCapNhat");
				con.setValue1(denngayCapNhat);
				con.setOperator("le");
				cons.add(con);
			}
			if (tinhTrang != null && !tinhTrang.equalsIgnoreCase("")) {
				ConditionReport con = new ConditionReport();
				con.setCol("tinhTrang");
				con.setValue1("%" + tinhTrang + "%");
				con.setOperator("like");
				cons.add(con);
			}
		
			int cc = tblDKMHKetquaService.counttblDKMHKetquaByCondition(cons);

			
			
			String q = "count(*) ,1 ";
			q += " FROM Tbl_DKMH_Ketqua as kq " +
			"   left join kq.hocVienId as hv " +
			"   left join kq.NLMHId as nl " +
			"   left join kq.NLMHId.monHocId as mh " +
			" WHERE 1=1 ";

	q += " AND kq.xacNhanYN=true ";
	q += " AND kq.ketQua not in  ('BH','HH') ";
			String sql = " " +
					" SELECT hv.MSSV, " +
					" hv.hoTen, " +
					" mh.ma, " +
					" mh.ten, " +
					" nl.ma, " +
					" 1, 1 , " + 
					" kq.ketQua, " +
					" kq.ketQuaLyDo " +
					
					" FROM Tbl_DKMH_Ketqua as kq " +
					"   left join kq.hocVienId as hv " +
					"   left join kq.NLMHId as nl " +
					"   left join kq.NLMHId.monHocId as mh " +
					" WHERE 1=1 ";

			sql += " AND kq.xacNhanYN=true ";
			sql += " AND kq.maDieuChinh in  ('BS','HC') ";
			
		if (dotDKId != 0) {
			sql += " AND kq.dotDKId.id = " + dotDKId + " ";
			q += " AND kq.dotDKId.id = " + dotDKId + " ";
		} else {
			return Action.ERROR;
		}
//		if (xacNhanYN != -1) {
//			if (xacNhanYN == 1) {
//			} else {
//
//				sql += " kq.xacNhanYN= false ";
//			}
//		}
		if (mssv != null && !mssv.equals("")) {
			sql += " AND hv.MSSV like '" + mssv + "%' ";
			q += " AND hv.MSSV like '" + mssv + "%' ";
		}
		if (maNhomLop != null && !maNhomLop.equals("")) {
			sql += " AND nl.ma = '" + maNhomLop + "' ";
			q += " AND nl.ma = '" + maNhomLop + "' ";
		}
		if (maMonHoc != null && !maMonHoc.equals("")) {
			sql += " AND mh.maMonHoc = '" + maMonHoc + "' ";
			q += " AND mh.maMonHoc = '" + maMonHoc + "' ";
		}

		if (ketQua != null && !ketQua.equalsIgnoreCase("")) {
			sql += " AND kq.ketqua = '" + ketQua + "' ";
			q += " AND kq.ketqua = '" + ketQua + "' ";
		}

		if (tinhTrang != null && !tinhTrang.equalsIgnoreCase("")) {
			sql += " AND kq.tinhTrang = '" + tinhTrang + "' ";
			q += " AND kq.tinhTrang = '" + tinhTrang + "' ";
		}
		
		sql += " ORDER BY hv.MSSV, mh.ten ";
		
		int soRecord = cc/1000 + 1;
//		List<Qresult> c = commonService.query(q, 2, 0, 0);
//		if(c != null && c.size() > 0){
//			soRecord = Integer.parseInt(c.get(0).getObjs().get(0).toString());
//			soRecord = soRecord/1000 + 1;
//		}

			if (filename == null || filename.equalsIgnoreCase("")){
				filename = "ketQuaDKMH.xls";
			}
			String filepath = "./file/" + filename;

			/*if (rowHeight == 0)
				rowHeight = defaultRowHeight;
			if (columnWidth == 0)
				columnWidth = defaultColWidth;
			*/
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Sheet 1");
			
			HSSFCellStyle headerCellStyle = workbook.createCellStyle(); 
	        HSSFFont headerFont = workbook.createFont();  
	        headerFont.setFontName(HSSFFont.FONT_ARIAL);  
	        headerFont.setFontHeightInPoints((short) 10);  
	        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        headerCellStyle.setFont(headerFont);  
	        
	        HSSFCellStyle cellStyle = workbook.createCellStyle(); 
	        HSSFFont cellFont = workbook.createFont();  
	        cellFont.setFontName("CALIBRI"); 
	        cellFont.setFontHeightInPoints((short) 11);   
	        cellStyle.setFont(cellFont); 
			
//			List<Object[]> data = mapData(tblDKMHKetquas);
	        
	        for(int j = 1; j <= soRecord; j++)
	        {
				List<Qresult> kqdk = commonService.query(sql,9, 1000*j, j);
	
		        List<Object[]> data = mapData2(kqdk);
				int rownum = 0;
				for (int i = 0; i < data.size(); i++) {
				    Row row = sheet.createRow(rownum++);
				    Object [] objArr = data.get(i);
				    int cellnum = 0;
				    for (Object obj : objArr) {
				        Cell cell = row.createCell(cellnum++);
				        if(obj instanceof Date) 
				            cell.setCellValue((Date)obj);
				        else if(obj instanceof Boolean)
				            cell.setCellValue((Boolean)obj);
				        else if(obj instanceof String)
				            cell.setCellValue((String)obj);
				        else if(obj instanceof Double)
				            cell.setCellValue((Double)obj);
				        
				        if(i == 0)
				        	cell.setCellStyle(headerCellStyle);
				        else cell.setCellStyle(cellStyle);
				    }
				}
				
				for (int ii = 0; ii < data.get(0).length; ii++) {				 
					sheet.autoSizeColumn(ii);
				}
				OutputStream bw = new FileOutputStream(filepath);
				workbook.write(bw);
				bw.close();
	        }
			
			//System.out.println("Excel written successfully..");
			return "download";
		} catch (Exception e) {
			System.out.println("Loi export Excel tbl_DKMH_Ketqua Action");
			e.printStackTrace();
			return Action.ERROR;
		}
	}
	
	
	private List<Object[]> mapData2(List<Qresult> tblDKMHKetquas) {
		
		List<Object[]> data = new ArrayList<Object[]>();
		data.add(new Object[] {"MSSV", "HoTen", "MaMonHoc","TenMonHoc", "MaNhom", "MaBaiTap", "NhomBaiTap", "KQ", "GhiChu"});

		java.text.DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		for (Qresult r : tblDKMHKetquas) {
//			TblDKMHKetqua tblDKMHKetqua = tblDKMHKetquas.get(r);
			
				String mSSV 		= (r.getObjs().get(0) == null ? "" : r.getObjs().get(0).toString());
				String hoTen 		= (r.getObjs().get(1) == null ? "" : r.getObjs().get(1).toString());
				String maMonHoc 	= (r.getObjs() .get(2)== null ? "" : r.getObjs().get(2).toString());
				String tenMonHoc 	= (r.getObjs() .get(3)== null ? "" : r.getObjs().get(3).toString());
				String maNhom 		= (r.getObjs().get(4) == null ? "" : r.getObjs().get(4).toString());
				String maBaiTap 	= (r.getObjs().get(5) == null ? "" : r.getObjs().get(5).toString());
				String nhomBaiTap 	= (r.getObjs().get(6) == null ? "" : r.getObjs().get(6).toString());
				String ketQua 		= (r.getObjs().get(7) == null ? "" : r.getObjs().get(7).toString());
				String ghiChu 		= (r.getObjs().get(8) == null ? "" : r.getObjs().get(8).toString());
				
				//String dieuChinh = (tblDKMHKetqua.getMaDieuChinh() == null ? "" : tblDKMHKetqua.getMaDieuChinh());
				//String xuLy = (tblDKMHKetqua.getTinhTrang() == null ? "" : tblDKMHKetqua.getTinhTrang());
	
				data.add(new Object[] {mSSV, hoTen, maMonHoc,tenMonHoc, maNhom, maBaiTap, nhomBaiTap, ketQua, ghiChu});
			
		}
		return data;
	}

	private List<Object[]> mapData(List<TblDKMHKetqua> tblDKMHKetquas) {
		
		List<Object[]> data = new ArrayList<Object[]>();
		data.add(new Object[] {"MSSV", "HoTen", "MaMonHoc", "MaNhom", "MaBaiTap", "NhomBaiTap", "KQ", "GhiChu"});

		java.text.DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		for (int r = 0; r < tblDKMHKetquas.size(); r++) {
			TblDKMHKetqua tblDKMHKetqua = tblDKMHKetquas.get(r);
			
			if(tblDKMHKetqua.getMaDieuChinh().equalsIgnoreCase("BS") || tblDKMHKetqua.getMaDieuChinh().equalsIgnoreCase("HC")){			
				String mSSV = (tblDKMHKetqua.getHocVienId() == null ? "" : tblDKMHKetqua.getHocVienId().getMSSV());
				String hoTen = (tblDKMHKetqua.getHocVienId() == null ? "" : tblDKMHKetqua.getHocVienId().getHoTen());
				String maMonHoc = (tblDKMHKetqua.getNLMHId() == null ? "" : tblDKMHKetqua.getNLMHId().getMonHocId().getMa());
				String maNhom = (tblDKMHKetqua.getNLMHId() == null ? "" : tblDKMHKetqua.getNLMHId().getMa());
				String maBaiTap = (tblDKMHKetqua.getNLMHBTId() == null ? "" : tblDKMHKetqua.getNLMHBTId().getMonHocId().getMa());
				String nhomBaiTap = (tblDKMHKetqua.getNLMHBTId() == null ? "" : tblDKMHKetqua.getNLMHBTId().getMa());
				String ketQua = (tblDKMHKetqua.getKetQua() == null ? "" : tblDKMHKetqua.getKetQua());
				String ghiChu = (tblDKMHKetqua.getKetQuaLyDo() == null ? "" : tblDKMHKetqua.getKetQuaLyDo());
				//String dieuChinh = (tblDKMHKetqua.getMaDieuChinh() == null ? "" : tblDKMHKetqua.getMaDieuChinh());
				//String xuLy = (tblDKMHKetqua.getTinhTrang() == null ? "" : tblDKMHKetqua.getTinhTrang());
	
				data.add(new Object[] {mSSV, hoTen, maMonHoc, maNhom, maBaiTap, nhomBaiTap, ketQua, ghiChu});
			}
		}
		return data;
	}
}
